Financial and economic data is mostly displayed and stored as time
series. The time series data in the csv file is in long
format
# libraries
library(data.table)
library(plotly)
## Loading required package: ggplot2
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:data.table':
##
## between, first, last
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(tidyr)
# read file
dt_prices <- fread("price_data.csv")
dt_prices
## stock date price
## 1: AMZN 2000-03-07 3.175
## 2: AMZN 2000-01-03 4.469
## 3: AMZN 2000-01-04 4.097
## 4: AMZN 2000-01-05 3.488
## 5: AMZN 2000-01-06 3.278
## ---
## 17357: MSFT 2022-12-23 238.730
## 17358: MSFT 2022-12-27 236.960
## 17359: MSFT 2022-12-28 234.530
## 17360: MSFT 2022-12-29 241.010
## 17361: MSFT 2022-12-30 239.820
# create wide
print("---> csv data in wide form")
## [1] "---> csv data in wide form"
dt_prices_wide <- dcast(dt_prices, date ~ stock, value.var = "price")
dt_prices_wide
## date AAPL AMZN MSFT
## 1: 2000-01-03 0.999 4.469 58.2813
## 2: 2000-01-04 0.915 4.097 56.3125
## 3: 2000-01-05 0.929 3.488 56.9063
## 4: 2000-01-06 0.848 3.278 55.0000
## 5: 2000-01-07 0.888 3.478 55.7188
## ---
## 5783: 2022-12-23 131.860 85.250 238.7300
## 5784: 2022-12-27 130.030 83.040 236.9600
## 5785: 2022-12-28 126.040 81.820 234.5300
## 5786: 2022-12-29 129.610 84.180 241.0100
## 5787: 2022-12-30 129.930 84.000 239.8200
# plot
fig1 <- plot_ly(dt_prices_wide, type = "scatter", mode = "lines") %>%
add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
layout(title = "Stock Price Series", yaxis=list(title="price"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig1
It may be difficult to compare the 3 price series above because the starting price is not the same. To overcome this, calculate the return series \(r_t\) for a stock and build a price series from a common base value.
The total return for a stock at time \(T\) is defined as
\[ TR(T) = 100 \times \Big[ \Pi_{i=1}^{t\leq T} \big( 1 + r_{t} \big)\Big]\]
Return at time \(t\) is
\[ r_t = \frac{p_t - p_{t-1}}{p_{t-1}}\]
Compare the Total Return series for the 3 stocks
dt_returns <- dt_prices_wide %>%
mutate(across(!date, ~ .x/lag(.x) -1)) %>%
drop_na()
fig2 <- plot_ly(dt_returns, type = "scatter", mode = "lines") %>%
add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
layout(title = "Stock Return Series", yaxis=list(title="returns"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig2
# calc total return
dt_cumulative_returns <- dt_returns %>%
mutate(across(!date, ~ 100*cumprod(1+.x)))
# plot
fig3 <- plot_ly(dt_cumulative_returns, type = "scatter", mode = "lines") %>%
add_trace(x = ~date, y = ~AAPL, name = "AAPL") %>%
add_trace(x = ~date, y = ~AMZN, name = "AMZN") %>%
add_trace(x = ~date, y = ~MSFT, name = "MSFT") %>%
layout(title = "Stock Total Return Seriess", yaxis=list(title="returns"), width = 1000)
## Warning: Specifying width/height in layout() is now deprecated.
## Please specify in ggplotly() or plot_ly()
fig3